Build Parent Child Hierarchies
Typically, hierarchies are made up of multiple attribute columns, with each column representing a different level of the hierarchy. For example, in an organizational hierarchy for a store, you may have one column for Store Manager, another column for Department Managers, and a third column for Team Members.
When the hierarchy is configured in Pyramid, it enables drill and expand functions; this allows users to traverse different levels of the hierarchy as needed.
Note: parent child hierarchies are available with an Enterprise license only.
A parent child hierarchy doesn't consist of discrete attribute columns in the source. Instead, each attribute in the entire hierarchy is stored in a single column; each attribute's location in the hierarchy is defined by the unique key of its parent attribute.
- Click here to learn more about parent child hierarchies.
- Click here to review examples of parent child hierarchies.
In the example below, we have an organization parent child hierarchy for a store. The Employee Name column contains the names of the Store Manager, Department Managers, and Team Members. The employees are organized into levels using the Employee Key and Parent Key columns. Each employee is assigned an employee key; all employees that have a parent in the hierarchy are assigned a parent key. Vilma, the Store Manager, has not been assigned a parent key because this is the top level (root) of the hierarchy.
Create Parent Child Hierarchies
Parent child hierarchies are constructed from the Hierarchy panel. You'll need to set the Type to Parent child (green highlight below) and then set the appropriate Rollup Type (blue highlight below). Next, add the required columns to the Attribute Selections (red highlight below).
Continue reading for details about the rollup type and attribute selections.
Rollup Type
Rollup Type determines how the parents will be calculated. Let's say we have a store with a Store Manager; under the Store Manager are two Department Managers; there are 10 Team Members working under each Department Manager. An organizational hierarchy for tracking sales for this store will be made up of the following levels: Store Manager, Department Managers, and Team Members.
Now we have a question of how sales should be calculated at the Department Manager and Store Manager levels. Should the Store Manager level display sales made by all its children (department managers and team members)? Or should it show sales made by the store manager and all her department managers and team members? Or should it show sales made by the store manager only?
The answer will generally depend on what kind of data you're working with, and what you want to show in the hierarchy.
Children
The total of all children is calculated by aggregating all the child values together. This total is displayed at the parent level. This option is generally used when the top level of the hierarchy doesn't have its own value; for instance, for accounting.
If we take our store, and we want to look at Net Profits, we need to subtract total expenses from total profits. The top level of the hierarchy is Net Profits; the second level is Total Revenues and Total Expenses; the third level contains all of the various revenues and expenses.
Net Profits, Total Revenues, and Total Expenses don't have any value until the query is resolved at runtime; at this point, the various revenues and expenses are rolled up, and total expenses are then subtracted from total revenues to find the value for Net Profits.
Self
Each level will show its own value only; values shown for parents will not include the child values.
Using our store sales example, the Store Manager level will display sales made by the store manager only; the Department Managers level will show sales made by department managers; and the Team Members level will show sales made by each team member.
This option is useful if we don't want to aggregate the parent and child levels; if each level should display a distinct value.
Self and Children
The parent level will show the total value of all its children AND itself.
Taking our store sales example, the Store Manager level will show all sales made by the store manager AND the department managers AND the team members. The Department Managers level will show all sales made by the department managers AND by the team members. And the Team Members level will show sales.
This option is useful if every level in the hierarchy has a distinct value, which we want to see combined with the values of its children. For instance, if all managers and team members are responsible for making sales.
Orphan Handling
An orphan is any child whose parent is an empty string or a value that doesn't exist in the child column. Use the Orphan Handling drop down to determine what will happen when there are orphans in the tree at the time that the model is processed.
Ignored
Orphan nodes are ignored and not added to the hierarchy.
Roots
All orphan nodes are used as root nodes.
Error
If there are any orphan nodes, the hierarchy will not be created.
Attribute Selections
Add the relevant columns from the required table in the 'Select Element' panel to the appropriate field in the Attribute Selections.
Child Key
The Child Key is the column containing the unique identifiers of hierarchy's attributes. In this example, this is the Employee Key column.
Parent Key
The Parent Key is the column that lists unique identifier of the parent of each attribute.
Using our store example, department manager Robin is a child of the store manager; Robin is assigned the parent key '1' as she is a direct child of the store manager whose unique identifier is 1. Robin's unique identifier is '3'; her team members are all assigned a parent key '3'.
Vilma is the store manager; she has not been assigned a parent key because there is no parent level above her.
Caption
The Caption is the column that contains the names of each attribute in the hierarchy. In our store example, these are the full names of each employee.
Sort (Optional)
Sorting is done alphabetically by default. You may have a different method by which you want to sort. In this case, you can add a sort column that uses numbers to sort the members in the hierarchy.
Unary Operator (Optional)
This options is enabled when the roll up type is set to Children or Self and Children.
The Unary Operator determines how data in the fact table will be calculated. By default, the value for each measure will be added. But there may be attributes in the hierarchy whose value should not be added to the total, but subtracted from it, or not rolled up to the parent at all. If the hierarchy contains attributes that should not be added, you can add a 'Unary Operator' column. This column specifies the operator type for each attribute.
The unary operator must be one of the following:
- " + " aggregate the value
- " - " subtract the value
- " ~ " the value should NOT be rolled up to the parent
If we take our store, and we want to look at Net Profits, we need to subtract total expenses from total profits. The top level of the hierarchy is Net Profit; the second level is Total Profits and Total Expenses; the third level contains all of the various profits and expenses.
The value for each item must be a positive numeric value. So how can we subtract expenses from profits if all values are added by default? We need to add a Unary Operator column to the hierarchy, specifying the operator type for each measure. We can then make sure that the operator for the Total Expenses level is set to minus " - " ensuring that the value of Net Profit will be Total Profits minus Total Expenses.
Displaying the Operator Column in the Query
We can break out the query using the Operator column, which will show the rollup logic that was used to calculate each hierarchy level. Note that when the Operator column is added to the query in Discover, it displays the operator that was used to rollup the values of the child levels. For example, in the grid below, Liabilities and Owners Equity (yellow highlight below) is subtracted from Assets to get the Balance Sheet value (blue highlight). However, we see that the operator for the Liabilities and Owners Equity is displayed as " + ". This indicates that the children of this level were aggregated together to find the value of the parent.
At the Net Income level, we see both a " + " and a " - ". This indicates that there are both children that were aggregated and subtracted. And indeed we see that the children of Net Income (orange highlights) include both positive and negative values. 'Taxes' for instance, has a value of 7,243,791.01 to be subtracted from the total. We also see that under Operating Profit and Other Income and Expenses are more values to be subtracted. Within Operating Profit, we find that the leaves (red highlight below) "Returns and Adjustments" and "Discounts" are values that are being subtracted. These 2 values add up to 6,606,960.38, which we see is the value being subtracted from Operating Profit.
We see that Net Income is 14,076,494.98 subtracted from 43,054,845.84. Fourteen million is the value of all negative values in the leaves; it is the sum of "Returns and Adjustments", "Discounts", and "Taxes".
Of course, when the Operator column is removed, we simply see the calculated totals for each level:
Include All Level
From the Attribute Selections, you can opt to include an "All" level at the top of the hierarchy.
This creates a new root level that calculates the total value of the hierarchy. This is useful if the hierarchy does not already contain a single root level.
